"""
Case Type   : 极致RTO支持备机可读
Case Name   : 传统集群，创建行存段页式range分区表，结合create table as,开启极致RTO场景下验证备机可读
Create At   : 2025/03
Owner       : haomeng
Description :
    1、集群内开启极致RTO特性，重启使其生效
    2、创建行存段页式range分区表，插入数据
    3、结合create table as语法创建表
    4、备机查看表数据
    5、对测试表进行部分DML操作，稍后再次在备机中查看表数据
    6、清理环境，恢复环境初始状态
Expect      :
    1、设置成功，重启成功
    2、创建成功，插入数据成功
    3、创建成功
    4、查看成功
    5、查看成功
    6、清理成功，恢复环境成功
History     :
"""

import os
import time
import unittest

from testcase.utils.Common import Common
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger


class RTOStandbyRead(unittest.TestCase):

    def setUp(self):
        self.log = Logger()
        self.log.info(f'----{os.path.basename(__file__)} start----')
        self.pri_sh = CommonSH('PrimaryDbUser')
        self.sta_sh = CommonSH('Standby1DbUser')
        self.constant = Constant()

        self.param_list = ['recovery_parse_workers', 'recovery_redo_workers']
        self.value_list = ['4', '4']
        self.default_value = []
        self.table_list = ['t_rto_018', 't_rto_018_one', 't_rto_018_two',
                           't_rto_018_one_new', 't_rto_018_two_new']

    def test_rto_standby_read(self):
        text = '-----step1:集群内开启极致RTO特性，重启使其生效;  expect:设置成功，重启成功-----'
        self.log.info(text)

        for param in self.param_list:
            value = self.pri_sh.execut_db_sql(f'show {param}')
            self.default_value.append(value.splitlines()[-2].strip())
        self.log.info(self.default_value)

        for param in self.param_list:
            set_param = self.pri_sh.execute_gsguc('set',
                                              self.constant.GSGUC_SUCCESS_MSG,
                                              f'{param} = {self.value_list[self.param_list.index(param)]}')
            self.log.info(set_param)
            self.assertTrue(set_param, '参数设置失败')

        self.log.info('---重启集群使参数生效---')
        restart = self.pri_sh.restart_db_cluster()
        self.log.info(restart)
        self.assertTrue(restart, '集群重启失败')

        self.log.info('---查看参数值修改是否成功---')
        for param in self.param_list:
            value = self.pri_sh.execut_db_sql(f'show {param};')
            self.assertEqual(value.splitlines()[-2].strip(),
                             self.value_list[self.param_list.index(param)], '参数未修改成功')

        text = '-----step2:创建行存段页式range分区表，插入数据  expect:创建成功，插入数据成功-----'
        self.log.info(text)

        sql1 = f"drop table if exists {self.table_list[0]};" \
               f"create table {self.table_list[0]}(id int, name varchar(200), age int, tel int) with(segment=on) " \
               f"partition by range(tel) " \
               f"(partition p1 values less than(5000), partition p2 values less than(10000), " \
               f"partition p3 values less than(15000), partition p4 values less than(20000), " \
               f"partition p5 values less than(25000), partition p6 values less than(30000), " \
               f"partition p7 values less than(40000), partition p8 values less than(50000)); " \
               f"declare    " \
               f"    i integer;" \
               f"begin" \
               f"    for i in 1..100000 loop" \
               f"    insert into {self.table_list[0]} values(random()*100,'name_'||random()*10,random()*100,random()*10000);" \
               f"    end loop;" \
               f"end;"
        self.log.info(sql1)
        res1 = self.pri_sh.execut_db_sql(sql1)
        self.log.info(res1)
        self.assertIn(self.constant.CREATE_TABLE_SUCCESS, res1, '建表失败')
        self.assertIn(self.constant.CREATE_ANONYMOUS_BLOCK_SUCCESS_MSG, res1, '匿名块执行失败，插入数据失败')

        text = '-----step3:结合create table as语法创建表  expect:创建成功-----'
        self.log.info(text)
        sql2 = f"drop table if exists {self.table_list[1]};" \
               f"drop table if exists {self.table_list[2]};" \
               f"create table {self.table_list[1]} as select * from {self.table_list[0]};" \
               f"create table {self.table_list[2]} as select * from {self.table_list[0]} where age > 35;"
        self.log.info(sql2)
        res2 = self.pri_sh.execut_db_sql(sql2)
        self.log.info(res2)
        self.assertEqual(2, res2.count(self.constant.INSERT_SUCCESS_MSG), '建表失败')

        text = '-----step4:备节点查看表数据  expect:查看成功-----'
        self.log.info(text)

        sql2 = f"select count(*) from {self.table_list[1]};" \
               f"select count(*) from {self.table_list[2]};"
        self.log.info(sql2)
        select_res = self.sta_sh.execut_db_sql(sql2)
        self.log.info(select_res)
        self.assertIn('100000', select_res.splitlines()[2].strip(), '查询结果不一致')
        self.assertNotIn('(0 rows)', select_res.splitlines()[-1], '查询结果为0')

        text = '-----step5:对测试表进行部分DML操作，稍后再次在备机中查看表数据  expect:查看成功，与主机数据一致-----'
        self.log.info(text)

        for table in self.table_list[4:]:
            sql3 = f'alter table {self.table_list[0]} rename to {table};' \
                   f'alter table {table} add new_col int,add new_col2 text first, ' \
                   f'add new_col3 float after new_col2;' \
                   f'update {table} set tel = tel * 2 where id%2 != 0 and id < 1000;' \
                   f'delete from {table} where id < 200;'
            self.log.info(sql3)
            res3 = self.pri_sh.execut_db_sql(sql3)
            self.log.info(res3)
            self.assertEqual(2, res3.count(self.constant.ALTER_TABLE_MSG), 'alter表失败')
            self.assertIn(self.constant.UPDATE_SUCCESS_MSG, res3, 'update表数据失败')
            self.assertIn(self.constant.DELETE_SUCCESS_MSG, res3, 'delete表数据失败')

        sql4 = f"select * from {self.table_list[4]} limit 10;" \
               f"select count(*) from {self.table_list[4]};"
        self.log.info(sql4)

        pri_res = self.pri_sh.execut_db_sql(sql4)
        self.log.info(pri_res)

        time.sleep(5)
        sta_res = self.sta_sh.execut_db_sql(sql4)
        self.log.info(sta_res)
        self.assertEqual(pri_res.splitlines()[-2], sta_res.splitlines()[-2], '查询结果不一致')

    def tearDown(self):
        text = '-----step6:清理环境，恢复环境初始状态  expect:清理成功，恢复环境成功-----'
        self.log.info(text)

        for table in self.table_list[3:]:
            drop = f"drop table {table} cascade;"
            self.log.info(drop)
            drop_res = self.pri_sh.execut_db_sql(drop)
            self.log.info(drop_res)

        for param in self.param_list:
            reset_param = self.pri_sh.execute_gsguc('set',
                                                    self.constant.GSGUC_SUCCESS_MSG,
                                                    f'{param} = {self.default_value[self.param_list.index(param)]}')
            self.log.info(reset_param)

        restart = self.pri_sh.restart_db_cluster(False)
        self.log.info(restart)

        for param in self.param_list:
            reset_value = self.pri_sh.execut_db_sql(f'show {param};')
            self.log.info(reset_value)
            self.assertEqual(reset_value.splitlines()[-2].strip(),
                             self.default_value[self.param_list.index(param)], '参数恢复失败')
        self.log.info(f'----{os.path.basename(__file__)} end----')
